package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.ArrayList;

import model.Mesa;

public class MesaDAO extends ModelDAO {

	private Connection connection;
	
	public MesaDAO (){
		super();
		connection = getConnection();
	}
	
	public void closeConnection(){
		super.closeConnection();
	}
	
	public Mesa select(int id) {
		Mesa m = null;
		try{
			String consulta = "SELECT * FROM MESAS WHERE ID=?";
			PreparedStatement statement = this.connection.prepareStatement(consulta);
			statement.setInt(1, id);
			ResultSet result = statement.executeQuery();
			while(result.next()){
				m = new Mesa();
				m.setId(result.getInt(1));
				m.setChairs(result.getInt(2));
				m.setLocation(result.getString(3));
				m.setActive(result.getInt(4));
				m.setBusy(result.getInt(5));
			}
			result.close();
		    statement.close();
		}
		catch(SQLException e){e.printStackTrace();}
		return m;
	}
	
	public void create(Mesa m){
		if(select(m.getId())!=null)return;
		
		try{
			String consulta = "INSERT INTO MESAS "
				+"(ACTIVE,CHAIRS,LOCATION,BUSY,ID)"
				+ "VALUES " + "(?,?,?,?,?)";
			PreparedStatement statement = this.connection.prepareStatement(consulta);
			
			try{statement.setInt(1, m.getActive());}
			catch(Exception e){e.printStackTrace();}
			
			try{statement.setInt(2, m.getChairs());}
			catch(Exception e){e.printStackTrace();}
			
			if(m.getLocation()!=null)statement.setString(3, m.getLocation());
			else {statement.setNull(3, java.sql.Types.VARCHAR);}
			
			try{statement.setInt(4, m.getBusy());}
			catch(Exception e){e.printStackTrace();}
			
			if(maxId()==-1){
				try{statement.setInt(5, 0);}
				catch(Exception e){e.printStackTrace();}
			}
			else{
				try{statement.setInt(5, maxId()+1);}
				catch(Exception e){e.printStackTrace();}
			}
			
		    statement.executeUpdate();
		    statement.close();
		}
		catch(SQLException e){e.printStackTrace();}	
	}

	public void update(Mesa m){
		if(select(m.getId())==null)create(m);
		
		try{
			String consulta = "UPDATE MESAS "+
				"SET CHAIRS = ?, LOCATION = ?, ACTIVE = ?, BUSY = ? "+" WHERE ID = ?";
			PreparedStatement statement = this.connection.prepareStatement(consulta);

			try{statement.setInt(1, m.getChairs());}
			catch(Exception e){e.printStackTrace();}
			
			if(m.getLocation()!=null)statement.setString(2, m.getLocation());
			else {statement.setNull(2, java.sql.Types.VARCHAR);}
			
			try{statement.setInt(3, m.getActive());}
			catch(Exception e){e.printStackTrace();}
			
			try{statement.setInt(4, m.getBusy());}
			catch(Exception e){e.printStackTrace();}
			
			try{statement.setInt(5, m.getId());}
			catch(Exception e){e.printStackTrace();}
			
			statement.executeUpdate();
		    statement.close();
		}
		catch(SQLException e){e.printStackTrace();}
	}
	
	public int maxId(){
		int maxId = -1;
		try{
			Statement statement = this.connection.createStatement();
			String consulta = "SELECT MAX(ID) FROM MESAS";
			ResultSet result = statement.executeQuery(consulta);
			while(result.next())
				maxId = result.getInt(1);
			result.close();
			statement.close();
		}
		catch(Exception e){e.printStackTrace();}
		return maxId;
	}
	
	public List<Mesa> getAll(){
		List<Mesa> mesas = new ArrayList<Mesa>();
		try{
			Statement statement = this.connection.createStatement();
			String consulta = "SELECT * FROM MESAS";
			ResultSet results = statement.executeQuery(consulta);
			while(results.next()){
				Mesa m = new Mesa();
				m.setId(results.getInt(1));
				m.setChairs(results.getInt(2));
				m.setLocation(results.getString(3));
				m.setActive(results.getInt(4));
				m.setBusy(results.getInt(5));
				if(m.getActive()==1)
					mesas.add(m);
			}
			results.close();
			statement.close();
		}
		catch(SQLException e){e.printStackTrace();}
		return mesas;
	}
	
	public void delete(int id){
		Mesa m = select(id);
		m.setActive(0);
		update(m);
	}
}
